﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Maltose.Data
{
    /// <summary>
    /// 用于生成 SQL 条件拼接
    /// </summary>
    public sealed class SqlPageString
    {
        /// <summary>
        /// T-SQL语句
        /// </summary>
        public StringBuilder TSQL { get; private set; } = new StringBuilder();

        /// <summary>
        /// T-SQL参数
        /// </summary>
        public Dictionary<string, object> Parameters { get; private set; } = new Dictionary<string, object>();

        #region 构造函数 

        /// <summary>
        ///  SQL 条件拼接
        /// </summary>
        public SqlPageString()
        {

        }

        /// <summary>
        /// 原 初始化条件
        /// </summary>
        /// <param name="value"></param>
        public SqlPageString(string value)
        {
            TSQL.Append(value);
        }

        /// <summary>
        /// 追加sql语句跟参数
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="arg0">参数</param>
        public SqlPageString(string sql, object arg0)
        {
            TSQL.AppendFormat(sql, arg0);
        }

        /// <summary>
        /// 生成 WHERE 1=1
        /// </summary>
        /// <returns></returns>
        public SqlPageString True()
        {
            TSQL.Append(" WHERE 1=1 ");
            return this;
        }


        /// <summary>
        /// 追加SQL语句
        /// </summary>
        /// <param name="format"></param>
        /// <returns></returns>
        public SqlPageString Append(string format)
        {
            TSQL.Append(format);
            return this;
        }

        /// <summary>
        ///  追加SQL语句跟参数
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="arg0">参数</param>
        /// <returns></returns>
        public SqlPageString AppendFormat(string sql, object arg0)
        {
            TSQL.AppendFormat(sql, arg0);
            return this;
        }

        /// <summary>
        /// 添加一组参数
        /// </summary>
        /// <param name="key">参数名字 不需要加入@符号</param>
        /// <param name="value">参数值</param>
        /// <returns></returns>
        public SqlPageString AddParameters(string key, object value)
        {
            Parameters.Add($"@{key}", value);
            return this;
        }

        /// <summary>
        /// 添加一组动态参数
        /// <para>支持单一的实体类 以及匿名类 new{}</para>
        /// </summary>
        /// <param name="values"></param>
        /// <returns></returns>
        public SqlPageString AddDynamicParameters(object values)
        {
            values.GetType().GetProperties().ToList().ForEach(x =>
            {
                Parameters.Add("@" + x.Name, x.GetValue(values));
            });
            return this;
        }


        #endregion


        #region 等于条件 运算符

        /// <summary>
        /// 等于
        /// </summary>
        /// <param name="sw">是否执行</param>
        /// <param name="key">字段名称</param>
        /// <param name="value">值</param>
        /// <param name="keyWords">T-SQL关键字 AND OR</param>
        /// <param name="bo">T-SQL运算符</param>
        /// <returns></returns>
        SqlPageString Equal(bool sw, string key, object value, string keyWords, string bo = "=")
        {
            if (!sw) { return this; }
            var parameKey = FormatKey(key);
            TSQL.Append($" {keyWords} {key}{bo}{parameKey} ");
            Parameters.Add(parameKey, value);
            return this;
        }

        /// <summary>
        /// 等于  AND  key=value
        /// </summary>
        /// <param name="sw">是否执行</param>
        /// <param name="key">字段名称</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        public SqlPageString AndEqual(bool sw, string key, object value)
        {
            return Equal(sw, key, value, SqlKeyWords.AND);
        }

        /// <summary>
        ///  等于  AND  key=value
        /// </summary>
        /// <param name="key">字段名称</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        public SqlPageString AndEqual(string key, object value)
        {
            return AndEqual(true, key, value);
        }



        /// <summary>
        /// 大于 生成 AND key>value
        /// </summary>
        /// <param name="sw"></param>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public SqlPageString AndGreater(bool sw, string key, object value)
        {
            return Equal(sw, key, value, SqlKeyWords.AND, SqlOperator.Greater);
        }

        /// <summary>
        ///  大于 生成 AND key>value
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public SqlPageString AndGreater(string key, object value)
        {
            return AndGreater(true, key, value);
        }

        /// <summary>
        /// 大于或者等于 生成 AND key>=value
        /// </summary>
        /// <param name="sw">是否执行</param>
        /// <param name="key">字段名称</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        public SqlPageString AndGreaterOrEqual(bool sw, string key, object value)
        {
            return Equal(sw, key, value, SqlKeyWords.AND, SqlOperator.GreaterOrEqual);
        }

        /// <summary>
        /// 大于或者等于 生成 AND key>=value
        /// </summary>
        /// <param name="key">字段名称</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        public SqlPageString AndGreaterOrEqual(string key, object value)
        {
            return AndGreaterOrEqual(true, key, value);
        }

        /// <summary>
        /// 小于 生成 AND key value
        /// </summary>
        /// <param name="sw">是否执行</param>
        /// <param name="key">字段名称</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        public SqlPageString AndLess(bool sw, string key, object value)
        {
            return Equal(sw, key, value, SqlKeyWords.AND, SqlOperator.Less);
        }

        /// <summary>
        /// 小于 生成 AND key value
        /// </summary>
        /// <param name="key">字段名称</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        public SqlPageString AndLess(string key, object value)
        {
            return AndLess(true, key, value);
        }

        /// <summary>
        ///  小于或者等于 生成 AND key value
        /// </summary>
        /// <param name="sw">是否执行</param>
        /// <param name="key">字段名称</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        public SqlPageString AndLessOrEqual(bool sw, string key, object value)
        {
            return Equal(sw, key, value, SqlKeyWords.AND, SqlOperator.LessOrEqual);
        }

        /// <summary>
        /// 小于或者等于 生成 AND key value
        /// </summary>
        /// <param name="key">字段名称</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        public SqlPageString AndLessOrEqual(string key, object value)
        {
            return AndLessOrEqual(true, key, value);
        }
        #endregion

        #region 或者等于 运算符

        /// <summary>
        /// 或者等于  OR key value
        /// </summary>
        /// <param name="sw">是否执行</param>
        /// <param name="key">字段名称</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        public SqlPageString OrEqual(bool sw, string key, object value)
        {
            return Equal(sw, key, value, SqlKeyWords.OR);
        }

        /// <summary>
        /// 或者等于  OR key value
        /// </summary>
        /// <param name="key">字段名称</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        public SqlPageString OrEqual(string key, object value)
        {
            return OrEqual(true, key, value);
        }

        /// <summary>
        /// 大于 生成 Or key>value
        /// </summary>
        /// <param name="sw">是否执行</param>
        /// <param name="key">字段名称</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        public SqlPageString OrGreater(bool sw, string key, object value)
        {
            return Equal(sw, key, value, SqlKeyWords.OR, SqlOperator.Greater);
        }

        /// <summary>
        /// 大于 生成 Or key>value
        /// </summary>
        /// <param name="key">字段名称</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        public SqlPageString OrGreater(string key, object value)
        {
            return OrGreater(true, key, value);
        }

        /// <summary>
        /// 大于或者等于 生成 Or key value
        /// </summary>
        /// <param name="sw">是否执行</param>
        /// <param name="key">字段名称</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        public SqlPageString OrGreaterOrEqual(bool sw, string key, object value)
        {
            return Equal(sw, key, value, SqlKeyWords.OR, SqlOperator.GreaterOrEqual);
        }

        /// <summary>
        /// 大于或者等于 生成 Or key value
        /// </summary>
        /// <param name="key">字段名称</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        public SqlPageString OrGreaterOrEqual(string key, object value)
        {
            return OrGreaterOrEqual(true, key, value);
        }

        /// <summary>
        /// 小于 生成 OR key
        /// </summary>
        /// <param name="sw">是否执行</param>
        /// <param name="key">字段名称</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        public SqlPageString OrLess(bool sw, string key, object value)
        {
            return Equal(sw, key, value, SqlKeyWords.OR, SqlOperator.Less);
        }

        /// <summary>
        /// 小于 生成 OR key value
        /// </summary>
        /// <param name="key">字段名称</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        public SqlPageString OrLess(string key, object value)
        {
            return OrLess(true, key, value);
        }

        /// <summary>
        /// 小于或者等于 生成 AND key
        /// </summary>
        /// <param name="sw">是否执行</param>
        /// <param name="key">字段名称</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        public SqlPageString OrLessOrEqual(bool sw, string key, object value)
        {
            return Equal(sw, key, value, SqlKeyWords.OR, SqlOperator.LessOrEqual);
        }

        /// <summary>
        /// 小于或者等于 生成 OR key
        /// </summary>
        /// <param name="key">字段名称</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        public SqlPageString OrLessOrEqual(string key, object value)
        {
            return OrLessOrEqual(true, key, value);
        }

        #endregion

        #region 模糊搜索

        /// <summary>
        /// 模糊搜索
        /// </summary>
        /// <param name="sw">是否执行</param>
        /// <param name="key">字段名称</param>
        /// <param name="value">值</param>
        /// <param name="keyWords">T-SQL关键字</param>
        /// <returns></returns>
        SqlPageString Like(bool sw, string key, object value, string keyWords)
        {
            if (!sw) { return this; }
            var parameKey = FormatKey(key);
            TSQL.Append($" {keyWords} {key} LIKE {parameKey} ");
            Parameters.Add(parameKey, value);
            return this;
        }

        /// <summary>
        /// 模糊搜索
        /// </summary>
        /// <param name="sw">是否执行</param>
        /// <param name="key">字段名称</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        public SqlPageString AndLike(bool sw, string key, object value)
        {
            return Like(sw, key, value, SqlKeyWords.AND);
        }

        /// <summary>
        /// 模糊搜索
        /// </summary>
        /// <param name="key">是否执行</param>
        /// <param name="value">字段名称</param>
        /// <returns></returns>
        public SqlPageString AndLike(string key, object value)
        {
            return AndLike(true, key, value);
        }

        /// <summary>
        /// 模糊搜索
        /// </summary>
        /// <param name="sw">是否执行</param>
        /// <param name="key">字段名称</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        public SqlPageString OrLike(bool sw, string key, object value)
        {
            return Like(sw, key, value, SqlKeyWords.OR);
        }

        /// <summary>
        /// 模糊搜索
        /// </summary>
        /// <param name="key">字段名称</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        public SqlPageString OrLike(string key, object value)
        {
            return OrLike(true, key, value);
        }

        #endregion


        #region 时间查询

        /// <summary>
        /// 时间查询
        /// </summary>
        /// <param name="sw">条件</param>
        /// <param name="key">键</param>
        /// <param name="start">开始时间</param>
        /// <param name="end">结束时间</param>
        /// <param name="keyWords">关键值</param>
        /// <returns></returns>
        SqlPageString Date(bool sw, string key, DateTime start, DateTime end, string keyWords)
        {
            if (!sw) { return this; }
            var startParameKey = $"@DateStart_{FormatDateKey(key)}";
            var endParameKey = $"@DateEnd_{FormatDateKey(key)}";
            Parameters.Add(startParameKey, start);
            Parameters.Add(endParameKey, end);
            TSQL.Append($" {keyWords} {key} BETWEEN {startParameKey} AND {endParameKey} ");
            return this;
        }

        /// <summary>
        /// 时间查询
        /// </summary>
        /// <param name="sw">是否执行</param>
        /// <param name="key">字段名称</param>
        /// <param name="start">开始时间</param>
        /// <param name="end">结束时间</param>
        /// <returns></returns>
        public SqlPageString Date(bool sw, string key, DateTime start, DateTime end)
        {
            return Date(sw, key, start, end, string.Empty);
        }


        /// <summary>
        /// 时间查询
        /// </summary>
        /// <param name="key"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public SqlPageString Date(string key, DateTime start, DateTime end)
        {
            return Date(true, key, start, end);
        }

        /// <summary>
        /// 时间查询
        /// </summary>
        /// <param name="sw">是否执行</param>
        /// <param name="key">字段名称</param>
        /// <param name="start">开始时间</param>
        /// <param name="end">结束时间</param>
        /// <returns></returns>
        public SqlPageString AndDate(bool sw, string key, DateTime start, DateTime end)
        {
            return Date(sw, key, start, end, SqlKeyWords.AND);
        }

        /// <summary>
        /// 时间查询
        /// </summary>
        /// <param name="key">字段名称</param>
        /// <param name="start">开始时间</param>
        /// <param name="end">结束时间</param>
        /// <returns></returns>
        public SqlPageString AndDate(string key, DateTime start, DateTime end)
        {
            return AndDate(true, key, start, end);
        }

        /// <summary>
        /// 时间查询
        /// </summary>
        /// <param name="sw">是否执行</param>
        /// <param name="key">字段名称</param>
        /// <param name="start">开始时间</param>
        /// <param name="end">结束时间</param>
        /// <returns></returns>
        public SqlPageString OrDate(bool sw, string key, DateTime start, DateTime end)
        {
            return Date(sw, key, start, end, SqlKeyWords.OR);
        }

        /// <summary>
        /// 时间查询
        /// </summary>
        /// <param name="key">字段名称</param>
        /// <param name="start">开始时间</param>
        /// <param name="end">结束时间</param>
        /// <returns></returns>
        public SqlPageString OrDate(string key, DateTime start, DateTime end)
        {
            return OrDate(true, key, start, end);
        }

        /// <summary>
        /// 时间操作
        /// </summary>
        /// <param name="sw">是否执行</param>
        /// <param name="key">字段名称</param>
        /// <param name="timeValues">时间值</param>
        /// <param name="split">分割符号</param>
        /// <param name="keyWords">关键字</param>
        /// <returns></returns>
        SqlPageString DateString(bool sw, string key, string timeValues, char split = '-', string keyWords = "")
        {
            var _value = timeValues.Split(split);
            return Date(sw, key, Convert.ToDateTime(_value[0].Trim()), Convert.ToDateTime(_value[1].Trim()), keyWords);
        }

        /// <summary>
        /// 时间操作
        /// </summary>
        /// <param name="sw">是否执行</param>
        /// <param name="key">字段名称</param>
        /// <param name="timeValues">时间值</param>
        /// <param name="split">分割符号</param>
        /// <returns></returns>
        public SqlPageString Date(bool sw, string key, string timeValues, char split = '-')
        {
            return DateString(sw, key, timeValues, split);
        }

        /// <summary>
        /// 时间操作
        /// </summary>
        /// <param name="key">字段名称</param>
        /// <param name="timeValues">时间值</param>
        /// <param name="split">分割符号</param>
        /// <returns></returns>
        public SqlPageString Date(string key, string timeValues, char split = '-')
        {
            return DateString(true, key, timeValues, split);
        }

        /// <summary>
        /// 时间操作
        /// </summary>
        /// <param name="sw">是否执行</param>
        /// <param name="key">字段名称</param>
        /// <param name="timeValues">时间值</param>
        /// <param name="split">分割符号</param>
        /// <returns></returns>
        public SqlPageString AndDate(bool sw, string key, string timeValues, char split = '-')
        {
            return DateString(sw, key, timeValues, split, SqlKeyWords.AND);
        }

        /// <summary>
        /// 时间操作
        /// </summary>
        /// <param name="key">字段名称</param>
        /// <param name="timeValues">时间值</param>
        /// <param name="split">分割符号</param>
        /// <returns></returns>
        public SqlPageString AndDate(string key, string timeValues, char split = '-')
        {
            return AndDate(true, key, timeValues, split);
        }

        /// <summary>
        /// 时间操作
        /// </summary>
        /// <param name="sw">是否执行</param>
        /// <param name="key">字段名称</param>
        /// <param name="timeValues">时间值</param>
        /// <param name="split">分割符号</param>
        /// <returns></returns>
        public SqlPageString OrDate(bool sw, string key, string timeValues, char split = '-')
        {
            return DateString(sw, key, timeValues, split, SqlKeyWords.OR);
        }

        /// <summary>
        /// 时间操作
        /// </summary>
        /// <param name="key">字段名称</param>
        /// <param name="timeValues">时间值</param>
        /// <param name="split">分割符号</param>
        /// <returns></returns>
        public SqlPageString OrDate(string key, string timeValues, char split = '-')
        {
            return OrDate(true, key, timeValues, split);
        }
        #endregion

        #region NOT IN 跟 IN 操作

        /// <summary>
        /// IN跟NOT INT 操作方法
        /// </summary>
        /// <param name="sw">是否执行</param>
        /// <param name="key">字段名称</param>
        /// <param name="value">值</param>
        /// <param name="keyWords">关键值</param>
        /// <param name="ob">IN NOT IN 关键字</param>
        /// <returns></returns>
        SqlPageString InAndNotIn(bool sw, string key, object value, string keyWords, string ob)
        {
            if (!sw) { return this; }
            var parameKey = FormatKey(key);
            TSQL.Append($" {keyWords} {key} {ob} {parameKey} ");
            Parameters.Add(parameKey, value);
            return this;
        }

        /// <summary>
        /// IN操作
        /// </summary>
        /// <param name="sw"></param>
        /// <param name="key">字段名称</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        public SqlPageString In(bool sw, string key, object value)
        {
            return InAndNotIn(sw, key, value, "", "IN");
        }

        /// <summary>
        /// IN操作
        /// </summary>
        /// <param name="sw">是否执行</param>
        /// <param name="key">字段名称</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        public SqlPageString AndIn(bool sw, string key, object value)
        {
            return InAndNotIn(sw, key, value, SqlKeyWords.AND, "IN");
        }

        /// <summary>
        /// IN操作
        /// </summary>
        /// <param name="key">字段名称</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        public SqlPageString AndIn(string key, object value)
        {
            return AndIn(true, key, value);
        }

        /// <summary>
        /// IN操作
        /// </summary>
        /// <param name="sw">是否执行</param>
        /// <param name="key">字段名称</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        public SqlPageString OrIn(bool sw, string key, object value)
        {
            return InAndNotIn(sw, key, value, SqlKeyWords.OR, "IN");
        }

        /// <summary>
        /// IN操作
        /// </summary>
        /// <param name="key">字段名称</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        public SqlPageString OrIn(string key, object value)
        {
            return OrIn(true, key, value);
        }

        /// <summary>
        /// NOT IN操作
        /// </summary>
        /// <param name="sw">是否执行</param>
        /// <param name="key">字段名称</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        public SqlPageString NotIn(bool sw, string key, object value)
        {
            return InAndNotIn(sw, key, value, "", "NOT IN");
        }

        /// <summary>
        /// NOT IN操作
        /// </summary>
        /// <param name="key">字段名称</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        public SqlPageString NotIn(string key, object value)
        {
            return NotIn(true, key, value);
        }

        /// <summary>
        ///  NOT IN操作
        /// </summary>
        /// <param name="sw">是否执行</param>
        /// <param name="key">字段名称</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        public SqlPageString AndNotIn(bool sw, string key, object value)
        {
            return InAndNotIn(sw, key, value, SqlKeyWords.AND, "NOT IN");
        }


        /// <summary>
        /// NOT IN操作
        /// </summary>
        /// <param name="key">字段名称</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        public SqlPageString AndNotIn(string key, object value)
        {
            return AndNotIn(true, key, value);
        }

        /// <summary>
        /// NOT IN操作
        /// </summary>
        /// <param name="sw">是否执行</param>
        /// <param name="key">字段名称</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        public SqlPageString OrNotIn(bool sw, string key, object value)
        {
            return InAndNotIn(sw, key, value, SqlKeyWords.OR, "NOT IN");
        }

        /// <summary>
        /// NOT IN操作
        /// </summary>
        /// <param name="key">字段名称</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        public SqlPageString OrNotIn(string key, object value)
        {
            return OrNotIn(true, key, value);
        }

        #endregion


        /// <summary>
        /// 重写 格式化成字符串 为了让内置自带的语法支持
        /// </summary>
        /// <returns></returns>
        public override string ToString()
        {
            return TSQL.ToString();
        }

        #region 私有
        /// <summary>
        /// 去掉特殊符号
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        string FormatKey(string key)
        {
            return $"@{ key.Replace(".", "_")}{Parameters.Count}";
        }

        /// <summary>
        /// 时间格式
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        string FormatDateKey(string key)
        {
            return $"{ key.Replace(".", "_")}{Parameters.Count}";
        }
        #endregion

    }
}
